Show or Hide Empties

It is not unusual to have sparse data in a data model. This, in turn, produces null or blank results when a user builds a query and the resulting visualization. By default, the "empties" are suppressed by Pyramid, to remove the empty 'noise' and sparsity from the query - which usually produces cleaner more useful analyses. However, there are times when a user wants to see the blank items. The Show /Hide Empties function allows a user to control this setting.

Note: There are differences in how empties are treated between OLAP and SQL data sources. Click here for more.

Accessing Show Empties

To show or hide empties:

• Go to the Query tab and toggle Show Empties.
• Click the Show Empties drop-down and select Show all empties or Hide all empties.

When you show empties, the selection is added to the panel beside the visualization. Click its context menu and select Hide all empties to turn it off.

OLAP options

Empties can also be shown by right clicking on an OLAP hierarchy chip in the drop zone to show / hide its empties.

The same functionality is also extended to all hierarchies in a given drop zone for OLAP data sources:

Empties Logic

"Zero" Cells

This functionality does NOT remove data points that have values in them. This includes data points that amount to "zero". Often, with model formatting, cells with zero value appear to be blank, but do have, in effect, a value. In this situation, the data point will NOT be hidden. Checking the raw result can make it easier to spot this effect.

OLAP vs SQL Treatment

Currently, there is NO difference between data sources when using the default "hide empties" default behavior: all null data points are suppressed. However, there is a difference in the way 'show empties' is handled between SQL data sources and OLAP data sources:

• In OLAP data sources, show empties will show all blank / null combinations across the different hierarchies that do not have any common transactional data points: effectively manufacturing blank values for items that have no existing relationship in the database.
• In SQL data sources, show empties will only show 'null value' combinations across different hierarchies that have common transactional data points. It does NOT manufacture blank values for items that have no existing relationship.

The tables below explain this difference:

Using this simple model of 2 dimension tables (years and countries) and 1 fact table:

The default hide empty mode produces the same result in both data source types:

But, with show empty on, SQL results would show this:

While, OLAP results would show this:

Notice that the OLAP / MDX result manufactures 2009-Australia, while the SQL / PQL result does not.

Hide Query Info

When showing empties, a caption will appear on the canvas indicating that empty cells are being shown for all hierarchies:

To hide this caption, while still showing empties, enable Hide Query Info from the Design ribbon:

Example